﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace ObjectModel
{
    public class BaseEntity<T>
    {

		string connectionString = @"Data Source=localhost\sqlexpress;Initial Catalog=gimps;Integrated Security=SSPI;";
		[Field(Name = "id", Insertable = false)]
		public Int32 Id { get; set; }
		[Field(Name = "removed", Insertable = false)]
        public Boolean Removed { get; set; }

		public IEnumerable<T> select(String conds, params String[] properties)
		{
			var conn = new SqlConnection(connectionString);
			var sql = getSelectSql(conds, properties);
			conn.Open();
			var adp = new SqlDataAdapter(new SqlCommand(sql, conn));
			var ds = new DataSet();
			adp.Fill(ds);
			conn.Close();
			var lst = new List<Object>();
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				var ass = Assembly.GetExecutingAssembly();
				var obj = ass.CreateInstance(typeof(T).FullName);
				if (properties.Count() == 0)
					properties = getSelectProperties(obj);
				foreach (var prop in properties)
					setValue(dr, ass, obj, prop);
				lst.Add(obj);
			}
			return lst.Cast<T>();
		}

		void setValue(DataRow dr, Assembly ass, object obj, string prop)
		{
			var names = prop.Split('.');
			var pInfo = ass.GetType(obj.GetType().FullName).GetProperty(names.FirstOrDefault());
			if (names.Count() == 1)
			{
				if (String.IsNullOrEmpty(getField(pInfo).Name))
					return;
				pInfo.SetValue(obj, Convert.ChangeType(dr[getField(pInfo).Name], pInfo.PropertyType), null);
				return;
			}
			Object inst = pInfo.GetValue(obj, null);
			if (inst == null)
				inst = ass.CreateInstance(pInfo.PropertyType.FullName);
			setValue(dr, ass, inst, prop.Replace(pInfo.Name + ".", ""));
			pInfo.SetValue(obj, inst, null);
		}
		public Boolean update()
		{
			var conn = new SqlConnection(connectionString);
			var sql = getUpdateSql();
			conn.Open();
			var obj = new SqlCommand(sql, conn).ExecuteScalar();
			conn.Close();
			Boolean ret = false;
			ret = obj != null && Boolean.TryParse(obj.ToString(), out ret);
			return ret;
		}
		public Boolean delete()
		{
			var conn = new SqlConnection(connectionString);
			var sql = getDeleteSql();
			conn.Open();
			var obj = new SqlCommand(sql, conn).ExecuteScalar();
			conn.Close();
			Boolean ret = false;
			ret = obj != null && Boolean.TryParse(obj.ToString(), out ret);
			return ret;
		}
		public Boolean insert()
		{
			var conn = new SqlConnection(connectionString);
			var sql = getInsertSql();
			conn.Open();
			var obj = new SqlCommand(sql, conn).ExecuteScalar();
			conn.Close();
			Boolean ret = false;
			ret = obj != null && Boolean.TryParse(obj.ToString(), out ret);
			return ret;
		}

		String getSelectSql(String conds, params String[] properties)
		{
			var type = this.GetType();
			var props = getProps(type, 0, properties);
			var sql = "select ";
			if (props == null || props.Count() == 0)
				sql += " * ";
			else
			{
				foreach (var p in props)
					sql += getAcronym(p.Value) + "." + getField(p.Key).Name + ", ";
				sql = sql.Remove(sql.Length - 2);
			}
			sql += " from " + getTableName(type) + " " + getAcronym(type);
			sql += " " + getJoins(type, 0, properties);
			if (!String.IsNullOrEmpty(conds))
				sql += " " + (conds.StartsWith("where") || conds.StartsWith(" where") ? conds : "where ");
			return sql;
		}

		String getAcronym(Type t)
		{
			return t.Name.Substring(0, 5).ToLower();
		}

		String getJoins(Type type, Int32 level, params String[] properties)
		{
			String join = "";
			var otherProps = properties.Where(p => p.Contains(".")).GroupBy(p => p.Split('.')[0]);
			var dic = new Dictionary<PropertyInfo, Type>();
			foreach (var op in otherProps)
			{
				var selectedProps = op.Select(o => o.Replace(op.Key + ".", "")).ToArray();
				var prop = type.GetProperty(op.Key);
				join += "inner join " + getTableName(prop.PropertyType) + " " + getAcronym(prop.PropertyType);
				join += " on " + getAcronym(prop.PropertyType) + ".id=" + getAcronym(type) + "." + getField(prop).Name;
				var internalJoins = getJoins(prop.PropertyType, level++, selectedProps);
				join += String.IsNullOrEmpty(internalJoins) ? null : " " + internalJoins;
			}
			return join;
		}
		Dictionary<PropertyInfo, Type> getProps(Type type, Int32 level, params String[] properties)
		{
			var thisProps = properties.Where(p => !p.Contains("."));
			var otherProps = properties.Where(p => p.Contains(".")).GroupBy(p=>p.Split('.')[0]);
			var dic = new Dictionary<PropertyInfo, Type>();
			var props = type.GetProperties().Where(p => thisProps.Any(pp => pp == p.Name));
			foreach (var p in props)
				dic.Add(p, type);
			foreach (var op in otherProps)
			{
				var selectedProps = op.Select(o=>o.Replace(op.Key+".", "")).ToArray();
				var prop = type.GetProperty(op.Key);
				var internalProps = getProps(prop.PropertyType, level++, selectedProps);
				if(internalProps == null || internalProps.Count == 0)
					throw new Exception("Propriedade inexistente...");
				foreach (var ip in internalProps)
					dic.Add(ip.Key, ip.Value);
			}
			return dic;
		}
		String getUpdateSql()
		{
			var type = this.GetType();
			var props = getInsertProperties(type.GetProperties());
			var sql = "update " + getTableName(type) + " set ";
			foreach (var p in props)
				sql += getField(p).Name + "='" + getValue(p).Replace("'", "''") + "', ";
			sql = sql.Remove(sql.Length - 2);
			sql += " where id=" + Id;
			return sql;
		}
		String getDeleteSql()
		{
			var type = this.GetType();
			var props = getInsertProperties(type.GetProperties());
			var sql = "update " + getTableName(type) + " set removed=" + Id;
			sql += " where id=" + Id;
			return sql;
		}
		String getInsertSql()
        {
            var type = this.GetType();
			var props = getInsertProperties(type.GetProperties());
			var sql = "insert into " + getTableName(type) + " (";
			var values = "(";
			foreach (var p in props)
			{
				sql += getField(p).Name + ", ";
				values += "'" + getValue(p).Replace("'", "''") + "', ";
			}
			values = values.Remove(values.Length - 2) + ")";
			sql = sql.Remove(sql.Length - 2) + ")";
			sql += " values ";
			sql += values;
            return sql;
        }

		String[] getSelectProperties(Object obj)
		{
			return obj.GetType().GetProperties().Select(p => p.Name).ToArray();
		}
		PropertyInfo[] getInsertProperties(PropertyInfo[] props)
		{
			return props.Where(p=>getField(p).Insertable).ToArray();
		}
        Field getField(PropertyInfo p)
        {
            return p.GetCustomAttributes(true).Cast<Field>().FirstOrDefault(a => a.TypeId.ToString().Contains("Field"));
        }
		String getValue(PropertyInfo p)
		{
			var obj = p.GetValue(this, null);
			if (obj == null)
				return "";
			if (obj is DateTime)
			{
				if ((obj as DateTime?).Value == DateTime.MinValue)
					return "";
				return DateTime.Parse(obj.ToString()).ToString("yyyy-dd-MM");
			}
			return obj.ToString();
		}
		String getTableName(Type t)
		{
			return t.GetCustomAttributes(true).Cast<Table>().FirstOrDefault(a => a.TypeId.ToString().Contains("Table")).Name;
		}
		public static T Cast<T>(object o) 
		{
			return (T)o;
		}
    }

    [System.AttributeUsage(System.AttributeTargets.Property)]
    public class Field : System.Attribute
    {
		Boolean _insertable = true;
        public String Name { get; set; }
        public Boolean Nullable { get; set; }
        public Int32 Size { get; set; }
        public Boolean IsOtherTable { get; set; }
        public Boolean IsNN { get; set; }
        public String NNTable { get; set; }
        public String PkId { get; set; }
        public String FkId { get; set; }
        public Boolean Insertable { get{ return _insertable;}set{_insertable= value;}}

        public String FriendlyName { get; set; }
    }
    [System.AttributeUsage(System.AttributeTargets.Class)]
    public class Table : System.Attribute
    {
        public String Name { get; set; }
        public String FriendlyName { get; set; }
    }
}
